//assemble state-level amenities

//population
use "$data/Amenities/coest00intalldata", clear
keep if yearref == 1
keep if agegrp == 99
collapse (sum) tot_pop, by(state)
destring state, replace
ren state statefips
ren tot pop
replace pop = pop/1000000
save "$temp/state_pops", replace

//weather
import delimited "$data/Amenities/cooling.csv", clear
keep v11 v9
ren v9 cooling 
ren v11 state_full
replace state_full = subinstr(state_full, "_", " ", .)
replace state_full = strproper(state_full)
merge 1:1 state_full using "$data/Crosswalks/state_fips_crosswalk", keep(match) nogen
replace cooling = cooling/1000
keep cooling statefips
save "$temp/state_weather", replace

//school characteristics
use "$data/Amenities/state_school_chars_dynamic", clear
keep stfips *2010
ren stfips statefips
ren govt_exp govt_exp
ren s_t_ratio s_t_ratio
save "$temp/state_schools", replace

//unemployment rate
import excel "$data/Amenities/emp-unemployment.xls", clear sheet("States") 
keep A AG AB AC AD AE AF AG AH AI AJ AK AL AM AN
drop if _n<7
ren A statefips


destring statefips, replace force
replace statefips = statefips/1000
drop if statefips == . | statefips == 0

local start = 2004
ds A*
foreach var in `r(varlist)'{
	local start `++start'
	ren `var' unemp`start'
}

reshape long unemp, i(statefips) j(year)
save "$temp/state_unemp", replace


//crime rates
import excel "$data/Amenities/10tbl05.xls", clear
keep A C E J
count
forval i = 1/`r(N)'{
 	replace A = A[_n-1] if A == "" in `i'
}
keep if C == "Rate per 100,000 inhabitants" 
drop C
ren A state_full
ren E violent
ren J property
destring violent property, replace
compress

forval i = 1/9{
	replace state_full = subinstr(state_full, "`i'", "", .)
}

replace state_full = strproper(state_full)
merge 1:1 state_full using "$data/Crosswalks/state_fips_crosswalk", keep(match) nogen
keep violent property statefips
save "$temp/state_crime", replace

//college employment rates
use year educ statefip perwt labforce using "$data/ACS/usa_00021", clear
ren statefip statefips
replace labforce = labforce - 1
gen college = (educ>=10)
collapse (mean) college [w = perwt], by(statefips year)
save "$temp/state_college_rates", replace

//assembly
use "$temp/state_college_rates", clear
merge 1:1 statefips year using "$temp/state_unemp", keep(match) nogen
merge m:1 statefips using "$temp/state_crime", keep(match) nogen
merge m:1 statefips using "$temp/state_pops", keep(match) nogen
merge m:1 statefips using "$temp/state_schools", keep(match) nogen
merge m:1 statefips using "$temp/state_weather", keep(match) nogen
save "$temp/state_amenities_all", replace


//pca version
ds year statefips, not
collapse (mean) `r(varlist)', by(statefips)
save "$temp/state_amenities_collapse", replace
ds statefips, not
pca `r(varlist)'
predict pc1 pc2 pc3, score
keep statefips pc*
save "$temp/state_amenities_pc", replace
//